1. Field of Invention
The present invention relates generally to the field of databases. More specifically, the present invention is related to a client-based index advisor.
2. Discussion of Related Art
Modern enterprises today deploy several different databases from multiple vendors to support a variety of applications. The task of managing a heterogeneous mix of database servers and supporting different applications is a daunting task indeed. The autonomic computing initiatives from the major database vendors have provided tools that help DBAs in this task. These tools come in the form of advisors that help the DBA figure out what indexes to build, how to partition the tables, what materialized views to build, and what type of statistics to maintain. These tools are extremely powerful and can be used to great effect by experienced DBAs. However, to a certain extent, the proliferation of these tools has shifted the complexity of managing these databases to understanding the vagaries of these individual tools, their interfaces, and their semantics.
The problem of index selection has been studied in much detail, and many major database vendors provide support for automating this task. Although the details of the approaches taken by the different vendors vary, the essential strategy is to enumerate possible indexes and evaluate the impact of adding that index on the workload using the optimizer's cost model. Approaches such as the one outlined in the paper to Bruno et al. titled “Automatic physical database tuning: A relaxation-based approach” and the paper to Papadomanolakis et al. titled “Efficient use of the query optimizer for automated database design” describe techniques that make the enumeration and evaluation process more efficient by reusing computation and minimizing expensive invocations of the optimizer. The sometimes difficult task of detecting when the index advisor might provide large performance gains has been addressed in the paper to Bruno et al. titled “To tune or not to tune?: a lightweight physical design alerter”. In many real situations, a detailed workload profile is difficult to collect.